// jjq
// 查找待本部门回复的工单数据
def replyWaitJson_jjq(Object parameter){
    def sql = '''
        select m1.PkId as myPkVal,WfId,MailPK,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,m2.WorkFormNO as myMwfNO,isUrge,
        m2.FromTopic as myMwfTopic,m2.FmContent as FmContent,SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,4),4)) dCntAName1, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,6),6)) dCntAName2, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,8),8)) dCntAName3, 
        SDeptName,convert(varchar(10),GetTime,20) as acDate,BackState as bkState,BackRemark as acRemark,
        subBackState cbackDesc,m2.SpareString as SpareString 
        from mWfSendBack m1 left join mMailWorkForm m2 on m1.WfId=m2.PkId left join mMailGetDeal m3 on m2.OriginFK=m3.PkId 
        where m1.SBDeptID = #{param1} and m1.SendState in(21,23) and m1.BackState in (0,12,22) and m1.IsDel=0 and m2.IsDel=0 
    '''
    if (parameter?.sdeptName)
        sql += " and m1.SDeptName =#{sdeptName} "
    if (parameter?.spareString)
        sql += " and m2.SpareString =#{spareString} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) <= #{endTime} "
    if(parameter?.backDesc)
        sql += " and m1.subBackState=#{backDesc} "
    if(parameter?.workFormNO)
        sql += " and m2.WorkFormNO = #{workFormNO} "
    if(parameter?.formType)
        sql += " and m1.isUrge = #{formType} "
    return sql
}

// px
// 查找待本部门回复的工单数据
def replyWaitJson_px(Object parameter){
    def sql = '''
        select m1.PkId as myPkVal,WfId,MailPK,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,m2.WorkFormNO as myMwfNO,isUrge,
        m2.FromTopic as myMwfTopic,m2.FmContent as FmContent,SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,4),4)) dCntAName1, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,6),6)) dCntAName2, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,8),8)) dCntAName3, 
        SDeptName,convert(varchar(10),GetTime,20) as acDate,BackState as bkState,BackRemark as acRemark,
        dbo.mWfNOBackDesc(WfId,#{param1}) cbackDesc,m2.SpareString as SpareString 
        from mWfSendBack m1 left join mMailWorkForm m2 on m1.WfId=m2.PkId left join mMailGetDeal m3 on m2.OriginFK=m3.PkId 
        where m1.SBDeptID = #{param1} and m1.SendState in(21,23) and m1.BackState in (0,12,22) and m1.IsDel=0 and m2.IsDel=0 
    '''
    if (parameter?.sdeptName)
        sql += " and m1.SDeptName =#{sdeptName} "
    if (parameter?.spareString)
        sql += " and m2.SpareString =#{spareString} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) <= #{endTime} "
    if(parameter?.backDesc)
        sql += " and replace(#{backDesc},'0','') = dbo.mWfNOBackDesc(WfId,#{param1}) "
    if(parameter?.workFormNO)
        sql += " and m2.WorkFormNO = #{workFormNO} "
    if(parameter?.formType)
        sql += " and m1.isUrge = #{formType} "
    return sql
}

// 查找待本部门回复的工单数据
def replyWaitJson_gxq(Object parameter){
    def sql = '''
        select m1.PkId as myPkVal,WfId,MailPK,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,m2.WorkFormNO as myMwfNO,isUrge,
        m2.FromTopic as myMwfTopic,m2.FmContent as FmContent,SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,4),4)) dCntAName1, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,6),6)) dCntAName2, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,8),8)) dCntAName3, 
        SDeptName,convert(varchar(10),GetTime,20) as acDate,BackState as bkState,BackRemark as acRemark,
        subBackState cbackDesc,m2.SpareString as SpareString,SendState 
        from mWfSendBack m1 left join mMailWorkForm m2 on m1.WfId=m2.PkId left join mMailGetDeal m3 on m2.OriginFK=m3.PkId 
        where m1.SBDeptID = #{param1} and m1.SendState in(21,23) and m1.BackState in (0,12,22) and m1.IsDel=0 and m2.IsDel=0 AND DATEDIFF(day, GETDATE(), m1.BackLimit) > -30 
    '''
    if (parameter?.sdeptName)
        sql += " and m1.SDeptName =#{sdeptName} "
    if (parameter?.spareString)
        sql += " and m2.SpareString =#{spareString} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) <= #{endTime} "
    if(parameter?.backDesc)
        sql += " and m1.subBackState=#{backDesc} "
    if(parameter?.workFormNO)
        sql += " and m2.WorkFormNO = #{workFormNO} "
    if(parameter?.formType)
        sql += " and m1.isUrge = #{formType} "
    return sql
}

//临期件
def dueWorkOrderCount(Object parameter) {
    def sql = '''
        SELECT count(1)
        FROM mWfSendBack m1 LEFT JOIN mMailWorkForm m2 ON m1.WfId = m2.PkId LEFT JOIN mMailGetDeal m3 ON m2.OriginFK = m3.PkId 
        WHERE m1.SBDeptID = #{parameter} AND DATEDIFF(hour,GetDate(),BackLimit) > 0 AND DATEDIFF(hour,GetDate(),BackLimit) < 48
        AND m1.IsDel = 0 AND m2.IsDel = 0 and m1.SendState in(21,23) and m1.BackState in (0,12,22) 
    '''
    return sql
}

// 审核本部门回复列表数据
def replyChkJsonData(Object parameter){
    def sql = '''
        select m1.PkId as PkId,m1.PkId as myPkVal,WfId,MailPK,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,m2.WorkFormNO as myMwfNO,m2.FromTopic as myMwfTopic,SDeptName,SeqName,SortName,DealName,SBDeptName,convert(varchar(10),BackLimit,20) as bkLimit,SBDeptName,convert(varchar(10),BackTime,20) as bkDate,BackState as chgBack,m2.SpareString as SpareString 
        from mWfSendBack m1 left join mMailWorkForm m2 on m1.WfId = m2.PkId and m1.IsDel = 0 and m2.IsDel = 0 
        left join tm_mWfSendBack_temp m3 on m1.PkId = m3.sbId 
        where m1.SBDeptID='304090' and m1.BackState=10 and m3.sbId is null and DATEDIFF(day,GetDate(),m1.BackLimit) > -30 
        '''
    if (parameter.workFormNO)
        sql += " AND m2.WorkFormNO = #{workFormNO} "
    if (parameter.sdeptName)
        sql += " AND m2.SDeptName =#{sdeptName} "
    if (parameter.opUserName)
        sql += " AND m2.SUserName =#{opUserName} "
    return sql;
}

//未回复工单数量
def countReplyChkJson(Object parameter){
    def sql = '''
        select count(1) from mWfSendBack t1 left join mMailWorkForm t2 on t1.WfId = t2.PkId and t1.IsDel = 0 and t2.IsDel = 0 
        left join tm_mWfSendBack_temp t3 on t1.PkId = t3.sbId 
        where t1.SBDeptID=cast(#{parameter} as varchar) and t1.BackState=10 and t3.sbId is null and DATEDIFF(day,GetDate(),t1.BackLimit) > -30 
        '''
    return sql;
}

//
def backPeerDeptAuditMJson_gxq(Object parameter){
    def sql = '''
            select PkId as myPkVal,WfId,MailPK,dbo.frMailWFNo(WfId) as myMwfNO,dbo.frMailWFTopic(WfId) as myMwfTopic,
            SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) bkLimit,SDeptName,
            (select k.SpareString from mMailWorkForm k where k.PkId=WfId) SpareString,
            convert(varchar(10),SendTime,20) sdDate,convert(varchar(10),GetTime,20) as bkDate,BackRemark from mWfSendBack 
            where SBDeptID=#{param1} and SendState = 50 and IsDel=0 AND DATEDIFF(day, GETDATE(), BackLimit) > -30 
        '''
    if (parameter.workFormNO)
        sql += " and dbo.frMailWFNo(WfId) = #{workFormNO} "
    if (parameter.sdeptName)
        sql += " and SDeptName =#{sdeptName} "
    return sql;
}